package org.jeecgframework.web.service.impl;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.Cinema;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.common.model.json.Highchart;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.util.DateUtils;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.core.util.oConvertUtils;
import org.jeecgframework.tag.vo.datatable.SortDirection;
import org.jeecgframework.web.service.CbsEsPlanIpServiceI;
import org.jeecgframework.web.service.ZyCommonService;
import org.jeecgframework.web.system.pojo.base.CbsEsPlanIpEntity;
import org.jeecgframework.web.system.pojo.base.TSDepart;
import org.jeecgframework.web.system.pojo.base.TSType;
import org.jeecgframework.web.system.pojo.base.TSTypegroup;
import org.jeecgframework.web.system.pojo.base.ZyCinemaEntity;
import org.jeecgframework.web.system.pojo.base.ZyContactsEntity;
import org.jeecgframework.web.system.pojo.base.ZyEquipmentSpareEntity;
import org.jeecgframework.web.system.pojo.base.ZyInspectionPlanEntity;
import org.jeecgframework.web.system.pojo.base.ZyPlanRecordEntity;
import org.jeecgframework.web.util.StaticValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.alibaba.fastjson.JSONObject;

@Service("cbsEsPlanIpService")
@Transactional
public class CbsEsPlanIpServiceImpl extends CommonServiceImpl implements CbsEsPlanIpServiceI {

	@Autowired
	private ZyCommonService zyCommonService;
	
 	public <T> void delete(T entity) {
 		super.delete(entity);
 		//执行删除操作配置的sql增强
		this.doDelSql((CbsEsPlanIpEntity)entity);
 	}
 	
 	public <T> Serializable save(T entity) {
 		Serializable t = super.save(entity);
 		//执行新增操作配置的sql增强
 		this.doAddSql((CbsEsPlanIpEntity)entity);
 		return t;
 	}
 	
 	public <T> void saveOrUpdate(T entity) {
 		super.saveOrUpdate(entity);
 		//执行更新操作配置的sql增强
 		this.doUpdateSql((CbsEsPlanIpEntity)entity);
 	}
 	
 	/**
	 * 默认按钮-sql增强-新增操作
	 * @param id
	 * @return
	 */
 	public boolean doAddSql(CbsEsPlanIpEntity t){
	 	return true;
 	}
 	/**
	 * 默认按钮-sql增强-更新操作
	 * @param id
	 * @return
	 */
 	public boolean doUpdateSql(CbsEsPlanIpEntity t){
	 	return true;
 	}
 	/**
	 * 默认按钮-sql增强-删除操作
	 * @param id
	 * @return
	 */
 	public boolean doDelSql(CbsEsPlanIpEntity t){
	 	return true;
 	}
 	
 	/**
	 * 替换sql中的变量
	 * @param sql
	 * @return
	 */
 	public String replaceVal(String sql,CbsEsPlanIpEntity t){
 		sql  = sql.replace("#{id}",String.valueOf(t.getId()));
 		sql  = sql.replace("#{ip_company_id}",String.valueOf(t.getIpCompanyId()));
 		sql  = sql.replace("#{ip_year}",String.valueOf(t.getIpYear()));
 		sql  = sql.replace("#{ip_month}",String.valueOf(t.getIpMonth()));
 		sql  = sql.replace("#{is_adjustment}",String.valueOf(t.getIsAdjustment()));
 		sql  = sql.replace("#{create_name}",String.valueOf(t.getCreateName()));
 		sql  = sql.replace("#{create_by}",String.valueOf(t.getCreateBy()));
 		sql  = sql.replace("#{create_date}",String.valueOf(t.getCreateDate()));
 		sql  = sql.replace("#{update_name}",String.valueOf(t.getUpdateName()));
 		sql  = sql.replace("#{update_by}",String.valueOf(t.getUpdateBy()));
 		sql  = sql.replace("#{update_date}",String.valueOf(t.getUpdateDate()));
 		sql  = sql.replace("#{UUID}",UUID.randomUUID().toString());
 		return sql;
 	}

	@Override
	public void judgeCanGenerateEsIp(HttpServletRequest request, AjaxJson j) {
		String generateSource = request.getParameter("generateSource");
		if(StringUtil.isNotEmpty(generateSource)){
			Calendar cl = Calendar.getInstance();
			int curMonth = cl.get(Calendar.MONTH);
			int nextYear = cl.get(Calendar.YEAR);
			if(generateSource.equals(StaticValue.DIC_GENERATE_SOURCE_AUTO)){
				nextYear++;
			}
			if(curMonth != 11 && generateSource.equals(StaticValue.DIC_GENERATE_SOURCE_AUTO)){//自动生成巡检计划页面点击生成巡检计划需要判断是不是12 月份
				j.setSuccess(false);
				j.setMsg("当前月份不是12月");
			}else{
				int count = zyCommonService.getCountBySql("SELECT COUNT(1) as total FROM cbs_es_plan_ip cip WHERE cip.ip_year = ? AND generate_source = ?", nextYear, generateSource);
				if(count <= 0){
					j.setSuccess(false);
					j.setMsg("["+ nextYear +"]年份没有可以生成巡检计划的数据");
				}
			}
		}else{
			j.setMsg("生成类型为空");
			j.setSuccess(false);
		}
	}

	class MyBean{
		private Integer ipMonth;
		private Boolean canMove = true;
		public MyBean(){};
		public MyBean(Integer ipMonth){
			this.ipMonth = ipMonth;
		}
		public MyBean(Integer ipMonth,Boolean canMove){
			this.ipMonth = ipMonth;
			this.canMove = canMove;
		}
		public Integer getIpMonth() {
			return ipMonth;
		}
		public void setIpMonth(Integer ipMonth) {
			this.ipMonth = ipMonth;
		}
		public Boolean getCanMove() {
			return canMove;
		}
		public void setCanMove(Boolean canMove) {
			this.canMove = canMove;
		}
	}
	
	@Override
	public void calcEsPlanIp(AjaxJson j, String departId) {
		String message = "计算完毕";
		boolean success = true;
		long be = System.currentTimeMillis();
		System.out.println("----------------------------------开始计算...");
		TSType tsType = zyCommonService.queryTsType(StaticValue.DIC_BJ_HDBP, StaticValue.DIC_BJ_HDBP_ZY);
		String[] zyCodeArr = tsType.getTypename().split(",");
		Calendar ca = Calendar.getInstance();
		int currYear = ca.get(Calendar.YEAR);
		int nextYear = currYear + 1;//设定的年份及时要生成巡检计划的年份，既下一年
		Date currYearEnd = DateUtils.str2Date(currYear + "-12-31", DateUtils.date_sdf);//设定年的前一年 的12月31日
		Date nextYearBegin = DateUtils.str2Date(nextYear + "-1-1", DateUtils.date_sdf);//设定年 的1月1日
		Date nextYearEnd = DateUtils.str2Date(nextYear + "-12-31", DateUtils.date_sdf);//设定年 的12月31日
		Date nNextYearBegin = DateUtils.str2Date((nextYear + 1) + "-1-1", DateUtils.date_sdf);//当前选定年 的下一年的1月1日
		
		StringBuffer conditionSql = new StringBuffer();
		conditionSql.append(" AND es.is_spare = 0");
		conditionSql.append(" AND es.server_state ='").append(StaticValue.DIC_SERVER_STATE_1001).append("' ");
		conditionSql.append(" AND (! ISNULL(es.belong_cinema_id) AND LENGTH(trim(es.belong_cinema_id)) > 1) ");
		conditionSql.append(" AND (! ISNULL(es.repair_end_date) AND LENGTH(trim(es.repair_end_date)) > 1) ");
		conditionSql.append(" AND (! ISNULL(es.send_sign) AND LENGTH(trim(es.send_sign)) > 1) ");
		conditionSql.append(" AND (es.repair_end_date >= '").append((currYear + 1) + "-1-1").append("' OR es.bp_no IN( ");
		for (int i = 0;i < zyCodeArr.length;i++ ) {
			if(i != zyCodeArr.length - 1){
				conditionSql.append("'"+ zyCodeArr[i] +"',");
			}else{
				conditionSql.append("'"+ zyCodeArr[i] +"'");
			}
		}
		conditionSql.append(" ) ) ");
		conditionSql.append(" AND es.residual_ip_count > 0 ");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	es.id AS esId, ");
		sql.append(" 	es.sn, ");
		sql.append(" 	es.repair_end_date AS repairEndDate, ");
		sql.append(" 	es.send_sign AS ipCompany, ");
		sql.append(" 	MONTH(es.send_date) AS sendDateMonth, ");
		sql.append(" 	MONTH(es.repair_end_date) AS repairEndDateMonth, ");
		sql.append(" 	cm.id AS cmId, ");
		sql.append(" 	cm.`name` AS cmName ");
		sql.append(" FROM ");
		sql.append(" 	zy_equipment_spare es ");
		sql.append(" LEFT JOIN zy_cinema cm ON cm.id = es.belong_cinema_id ");
		sql.append(" WHERE 1=1 ");
		sql.append(conditionSql);
		sql.append(" GROUP BY cmId, ipCompany, esId, sn, repairEndDate, sendDateMonth, repairEndDateMonth, cmName ");//保证相同的影院必须！！！！！！在一起
		sql.append(" ORDER BY cmId");
		
		List<Map<String, Object>> esMapList = super.findForJdbc(sql.toString());
		System.out.println("一共有" + esMapList.size() + "条符合规则的设备数据");
		
		String lastCmId = ""/*上一次影院id*/,currCmId = ""/*当前影院id*/,currEsId/*当前设备Id*/,currIpCompany/*当前巡检单位*/;
		Integer repairEndDateMonth = 12;
		Date repairEndDate = null;
		Map<String, Map<String, MyBean>> cinemaIpCompanyMonth = new HashMap<String, Map<String, MyBean>>();//{影院：{巡检单位：[]}}；巡检单位对应巡检月份
		Map<String, MyBean> companyIpMonthMap = new HashMap<String, MyBean>();//巡检单位对应巡检月份(临时用，每各新的影院都重新初始化)
		Map<String, Object> esMap = null;

		sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	es.id, es.belong_cinema_id AS cmId, es.send_sign AS ipCompany ");
		sql.append(" FROM ");
		sql.append(" 	zy_equipment_spare es ");
		sql.append(" WHERE  1=1 ");
		sql.append(conditionSql);
		sql.append(" GROUP BY ");
		sql.append(" 	es.belong_cinema_id, ");
		sql.append(" 	es.send_sign ");
		List<Map<String, Object>> cmCompanyMapList = super.findForJdbc(sql.toString());
		try {
				
			/**
			 * 初始化影院对应巡检月份
			 * 找到设备中按影院分组 之后的巡检单位
			 */
			for (Map<String, Object> cmCompanyMap : cmCompanyMapList) {
				currCmId = cmCompanyMap.get("cmId").toString();
				if(null == cinemaIpCompanyMonth.get(currCmId)){
					cinemaIpCompanyMonth.put(currCmId, new HashMap<String, MyBean>());
				}
				cinemaIpCompanyMonth.get(currCmId).put(cmCompanyMap.get("ipCompany").toString(), null);
			}
			
			companyIpMonthMap = new HashMap<String, MyBean>();//初始化
			lastCmId = "";//初始化
			System.out.println("共：" + cinemaIpCompanyMonth.size()+"个影院！");
			if(null != esMapList && esMapList.size() > 0){
				System.out.println("开始计算b类情况...");
				for (int i = 0;i < esMapList.size();i++) {
					esMap = esMapList.get(i);
					currCmId = esMap.get("cmId").toString();
					currIpCompany = esMap.get("ipCompany").toString();
					repairEndDate = DateUtils.str2Date(esMap.get("repairEndDate").toString(), DateUtils.date_sdf);
					repairEndDateMonth = Integer.valueOf(esMap.get("repairEndDateMonth").toString());
					
					long repairEndDateTime = repairEndDate.getTime();
					if((!lastCmId.equals("") && !lastCmId.equals(currCmId))){//不是第一次循环，并且当前和上一个不同  or 1,2,3
						companyIpMonthMap = new HashMap<String, MyBean>();
					}
					if(null == companyIpMonthMap.get(currIpCompany)){//如果当前巡检单位的巡检月份没有初始化
						companyIpMonthMap.put(currIpCompany, new MyBean(repairEndDateMonth == 1 ? repairEndDateMonth : repairEndDateMonth -1));//则初始化为（保修截止日期 - 1）
					}
					if(repairEndDateTime >= nextYearBegin.getTime() && repairEndDateTime <= nextYearEnd.getTime()){//大于等于下一年1月1日 & 小于等于下一年12月31日（在下一年期间）
						if(null == cinemaIpCompanyMonth.get(currCmId).get(currIpCompany) ||
								cinemaIpCompanyMonth.get(currCmId).get(currIpCompany).getIpMonth() > repairEndDateMonth - 1){//如果当前影院的巡检单位的巡检月份小于 （保修截止日期 - 1）
							companyIpMonthMap.put(currIpCompany, new MyBean(repairEndDateMonth == 1 ? repairEndDateMonth : repairEndDateMonth -1));//保存最小月份
							cinemaIpCompanyMonth.get(currCmId).putAll(companyIpMonthMap);
						}
					}
					lastCmId = currCmId;//记录本次影院ID
				}
				
				sql = new StringBuffer();
				sql.append(" SELECT ");
				sql.append(" 	ip.equipment_spare_id AS esId, MONTH(re.inp_end_time) AS ipMonth ");
				sql.append(" FROM ");
				sql.append(" 	zy_inspection_plan ip ");
				sql.append(" LEFT JOIN ");
				sql.append(" 	zy_inspection_report re on (ip.id = re.inspection_plan_id) ");
				sql.append(" WHERE ");
				sql.append(" 	YEAR (ip.ip_plan_date) = ").append(nextYear - 1).append(" ");//当前年份（指的是所选年份的上一年）
				sql.append(" AND ip.state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' ");
				sql.append(" GROUP BY ");
				sql.append(" 	ip.equipment_spare_id ");
				List<Map<String, Object>> esHadIpMapList = super.findForJdbc(sql.toString());
				System.out.println("开始计算c类情况...");
				//初始化数据
				lastCmId = "";
				companyIpMonthMap = new HashMap<String, MyBean>();
				
				for (int i = 0; i < esMapList.size(); i++) {
					esMap = esMapList.get(i);
					currCmId = esMap.get("cmId").toString();
					currEsId = esMap.get("esId").toString();
					currIpCompany = esMap.get("ipCompany").toString();
					repairEndDate = DateUtils.str2Date(esMap.get("repairEndDate").toString(), DateUtils.date_sdf);
					long repairEndDateTime = repairEndDate.getTime();
					
					if((!lastCmId.equals("") && !lastCmId.equals(currCmId))){//不是第一次循环，并且当前和上一个不同  or 1,2,3
						companyIpMonthMap = new HashMap<String, MyBean>();
					}
					//如果当前影院当前巡检单位还没有设定巡检月份
					if(null == cinemaIpCompanyMonth.get(currCmId).get(currIpCompany) && repairEndDateTime >= nNextYearBegin.getTime()){//大于选定年下一年的12月31日（在选定年下下年1月1日之后）
						boolean thisEsHadIpThisYear = false;
						for (Map<String, Object> map : esHadIpMapList) {
							if(null != map && map.get("esId").toString().equals(currEsId)){//如果设备有本年巡检计划
								thisEsHadIpThisYear = true;
								Integer esHadIpMonth = Integer.valueOf(map.get("ipMonth").toString());//本年巡检计划月份
								if(null == companyIpMonthMap.get(currIpCompany) || companyIpMonthMap.get(currIpCompany).getIpMonth() > esHadIpMonth){
									companyIpMonthMap.put(currIpCompany, new MyBean(esHadIpMonth));//保存最小月份
									cinemaIpCompanyMonth.get(currCmId).putAll(companyIpMonthMap);
								}
								break;
							}
						}
						if(!thisEsHadIpThisYear && StringUtil.isNotEmpty(esMap.get("sendDateMonth"))){//如果没有本年巡检计划，取设备出货日期
							companyIpMonthMap.put(currIpCompany, new MyBean(Integer.valueOf(esMap.get("sendDateMonth").toString())));
							cinemaIpCompanyMonth.get(currCmId).putAll(companyIpMonthMap);
						}
					}
					lastCmId = currCmId;//记录本次影院ID
				}
				
				System.out.println("开始计算d类情况...");
				//初始化数据
				lastCmId = "";
				
				for (int i = 0; i < esMapList.size(); i++) {
					esMap = esMapList.get(i);
					currCmId = esMap.get("cmId").toString();
					currEsId = esMap.get("esId").toString();
					currIpCompany = esMap.get("ipCompany").toString();
					long repairEndDateTime = DateUtils.str2Date(esMap.get("repairEndDate").toString(), DateUtils.date_sdf).getTime();
					repairEndDateMonth = Integer.valueOf(esMap.get("repairEndDateMonth").toString());
					
					if(repairEndDateTime <= currYearEnd.getTime()){//小于等于选定前一年的12月31日
						//if(!currCmId.equals(lastCmId)){//跳过所有相同影院，相同的影院只进来一次 ？？
						// Modify Start 将中影续保设备的最终巡检月份由10月改为9月。并参考本年巡检月份设置。 by Amy.Li 2017.12.27
							if(null != cinemaIpCompanyMonth.get(currCmId).get(currIpCompany)){//如果有影院的巡检月份
								if(cinemaIpCompanyMonth.get(currCmId).get(currIpCompany).getIpMonth() == 9){//影院的巡检月份小于等于10月时，影院的巡检月份不变
									cinemaIpCompanyMonth.get(currCmId).get(currIpCompany).setCanMove(false);
								}else if(cinemaIpCompanyMonth.get(currCmId).get(currIpCompany).getIpMonth() > 9){
										cinemaIpCompanyMonth.get(currCmId).put(currIpCompany,new MyBean(9));//大于9月都设置成9月
								}
							}else if(null == cinemaIpCompanyMonth.get(currCmId).get(currIpCompany)){//当前影院还没有设定巡检月份
								
								boolean thisEsHadIpThisYear = false;
								for (Map<String, Object> map : esHadIpMapList) {
									if(null != map && map.get("esId").toString().equals(currEsId)){//如果设备有本年巡检计划
										thisEsHadIpThisYear = true;
										Integer esHadIpMonth = Integer.valueOf(map.get("ipMonth").toString());//本年巡检计划月份
										esHadIpMonth = esHadIpMonth > 9 ? 9 : esHadIpMonth;
										if(null == cinemaIpCompanyMonth.get(currCmId).get(currIpCompany) || cinemaIpCompanyMonth.get(currCmId).get(currIpCompany).getIpMonth() > esHadIpMonth){
											cinemaIpCompanyMonth.get(currCmId).put(currIpCompany,new MyBean(esHadIpMonth));//保存最小月份
										}
										break;
									}
								}
								if(!thisEsHadIpThisYear){//没有本年巡检计划时，巡检月份为9月
									cinemaIpCompanyMonth.get(currCmId).put(currIpCompany,new MyBean(9));//大于9月都设置成9月
								}
							}
						// Modify End 将中影续保设备的最终巡检月份由10月改为9月。并参考本年巡检月份设置。 by Amy.Li 2017.12.27
						//}
					}
					lastCmId = currCmId;//记录本次影院ID
				}
				
				/** - 未确定巡检月份的影院 - （输出用,可删除）*/
				/*String last = "";
				int count = 0;
				for (String cinema : cinemaIpCompanyMonth.keySet()) {
					Map<String, Integer> companyMonthMap = cinemaIpCompanyMonth.get(cinema);
					for (String company : companyMonthMap.keySet()) {
						if(null == companyMonthMap.get(company)){
							//System.out.println("影院ID:"+ cinema + "，巡检单位ID:" + company);
							if(!last.equals("") && !cinema.equals(last) || count == companyMonthMap.size() - 1){
								System.out.println("影院ID：" + last);
							}
							last = cinema;
						}
					}
					count ++;
				}*/
				/** - 未确定巡检月份的影院 - */
				
				
				List<TSDepart> tsDeparts = super.findHql("from TSDepart where orgType = ?", StaticValue.DIC_ORG_TYPE_THIRD_PARTY);
				//初始化一个巡检单位每个月的计划产能
				Map<String, Map<Integer, List<CbsEsPlanIpEntity>>> initCpMonthIpsMap = initCompanyMonthPro(tsDeparts);
				
				Map<String, Map<Integer, List<Cinema>>> cpMonthCmMap = new HashMap<String, Map<Integer,List<Cinema>>>();//巡检单位{月份{影院[]}}
				//设置设备的巡检单位，巡检月份，巡检年份...
				for (Map<String,Object> map : esMapList) {
					CbsEsPlanIpEntity esPlanIp = new CbsEsPlanIpEntity();
					esPlanIp.setGenerateSource(StaticValue.DIC_GENERATE_SOURCE_AUTO);//标识来源
					esPlanIp.setIpYear(nextYear);
					ZyEquipmentSpareEntity es = new ZyEquipmentSpareEntity();
					es.setId(map.get("esId").toString());
					esPlanIp.setEquipmentSpare(es);
					repairEndDate = DateUtils.str2Date(map.get("repairEndDate").toString(), DateUtils.date_sdf);
					long repairEndDateTime = repairEndDate.getTime();
					if(repairEndDateTime >= nextYearBegin.getTime() && repairEndDateTime <= nextYearEnd.getTime()){
						esPlanIp.setIsAdjustment("0");
					}else{
						esPlanIp.setIsAdjustment("1");
					}
					esPlanIp.setIpCompanyId(map.get("ipCompany").toString());
					esPlanIp.setCinemaId(map.get("cmId").toString());
					if(null != cinemaIpCompanyMonth.get(esPlanIp.getCinemaId()).get(esPlanIp.getIpCompanyId())){
						esPlanIp.setIpMonth(cinemaIpCompanyMonth.get(esPlanIp.getCinemaId()).get(esPlanIp.getIpCompanyId()).getIpMonth());
						esPlanIp.setCanMove(cinemaIpCompanyMonth.get(esPlanIp.getCinemaId()).get(esPlanIp.getIpCompanyId()).getCanMove());
					}
					if(null != esPlanIp.getIpMonth()){
						if(null == initCpMonthIpsMap.get(esPlanIp.getIpCompanyId())){
							TSDepart depart = super.get(TSDepart.class, esPlanIp.getIpCompanyId());
							if(null == depart){
								message = "没有ID为["+ esPlanIp.getIpCompanyId() +"]的组织机构的信息";
								success = false;
								break;
							}
							message = "服务商["+depart.getDepartname()+"]的计划产能没有设置";
							success = false;
							break;
						}
						if(null == initCpMonthIpsMap.get(esPlanIp.getIpCompanyId()).get(esPlanIp.getIpMonth())){
							message = "服务商["+super.get(TSDepart.class, esPlanIp.getIpCompanyId()).getDepartname()+"] "+nextYear+" 年 "+esPlanIp.getIpMonth()+" 月的计划产能没有设置";
							success = false;
							break;
						}
						initCpMonthIpsMap.get(esPlanIp.getIpCompanyId()).get(esPlanIp.getIpMonth()).add(esPlanIp);
						//保存一个 巡检单位{月份{影院[]}} 这样格式的map
						if(null != cpMonthCmMap.get(esPlanIp.getIpCompanyId())){
							if(null == cpMonthCmMap.get(esPlanIp.getIpCompanyId()).get(esPlanIp.getIpMonth().toString())){
//								cpMonthCmMap.get(esPlanIp.getIpCompanyId()).get(esPlanIp.getIpMonth()).add(esPlanIp.getCinemaId());
//							}else{
								cpMonthCmMap.get(esPlanIp.getIpCompanyId()).put(esPlanIp.getIpMonth(), new ArrayList<Cinema>());
							}
						}else{
							cpMonthCmMap.put(esPlanIp.getIpCompanyId(), new HashMap<Integer, List<Cinema>>());
						}
					}
				}
				
				//是否每个巡检单位每个月份的计划产能都正确设置了
				if(success){
					
					sql = new StringBuffer();
					sql.append(" SELECT ");
					sql.append(" 	plan_capacity AS planCapacity, MONTH(years) AS companyMonth, service_provider AS ipCompany");
					sql.append(" FROM ");
					sql.append(" 	cbs_service_capacity ");
					sql.append(" WHERE ");
					sql.append(" 	YEAR(years) = ").append(nextYear).append(" ");
					sql.append(" GROUP BY ");
					sql.append(" 	service_provider, ");
					sql.append(" 	years ");
					sql.append(" ORDER BY ");
					sql.append(" 	service_provider, ");
					sql.append(" 	years ");
					List<Map<String, Object>> serviceCapacityMapList = super.findForJdbc(sql.toString());
					
					Map<String, Map<Integer, Integer>> companyPlanIpsMap = new HashMap<String, Map<Integer,Integer>>();//巡检单位每个月的计划产能
					if(null != serviceCapacityMapList && serviceCapacityMapList.size() > 0){
						/*
						 * 初始化巡检单位每个月的计划产能
						 */
						for (Map<String, Object> companyMap : serviceCapacityMapList) {
							String currCompany = companyMap.get("ipCompany").toString();
							if(null == companyPlanIpsMap.get(currCompany)){
								companyPlanIpsMap.put(currCompany,new HashMap<Integer,Integer>());
							}
							companyPlanIpsMap.get(currCompany).put(Integer.valueOf(companyMap.get("companyMonth").toString()),Integer.valueOf(companyMap.get("planCapacity").toString()));
						}
						
						for (String company : companyPlanIpsMap.keySet()) {//巡检单位对应的每个月的计划产能{巡检单位：{每个月：{计划产能}}}
							Map<Integer, Integer> cpMap = companyPlanIpsMap.get(company);
							success = true;
							for (int m = 1; m < 12; m++) {//全年12个月，只判断到11 月，因为12月超出了 需要人工调整.顺序从1月开始往后按影院为单位挪动
								if(null != cpMap.get(m) && StringUtil.isNotEmpty(cpMap.get(m).toString())){
									int monthIndex = 0; //存储取得从大到小排序的影院的index的记录
									sortCpMonthMap(initCpMonthIpsMap,cpMonthCmMap);//排序
									compare(initCpMonthIpsMap,company,m,Integer.valueOf(cpMap.get(m).toString()),cpMonthCmMap,monthIndex);
								}else{
									message = "服务商["+super.get(TSDepart.class, company).getDepartname()+"] "+nextYear+" 年 "+m+" 月的计划产能没有设置";
									success = false;
									break;
								}
							}
							if(!success){
								break;
							}
						}
						if(success){
							List<CbsEsPlanIpEntity> esPlanIps = new ArrayList<CbsEsPlanIpEntity>();
							for(Map<Integer,List<CbsEsPlanIpEntity>> cm : initCpMonthIpsMap.values()){
								for (List<CbsEsPlanIpEntity> cips : cm.values()) {
									esPlanIps.addAll(cips);
								}
							}
							System.out.println("开始删除原有数据...");
							super.executeSql(" DELETE FROM cbs_es_plan_ip WHERE generate_source ='"+ StaticValue.DIC_GENERATE_SOURCE_AUTO +"' ");//先删除原有自动生成的数据
							System.out.println("删除原有数据完毕");
							System.out.println("开始保存" + esPlanIps.size() + "条数据");
							super.batchSave(esPlanIps);
							System.out.println("保存新数据完毕");
						}
						
					}else{
						message = "没有设置服务商计划产能";
						success = false;
					}
					if(success){
						System.out.println("开始计算页面柱状图数据...");
						calcHighchartData(j, initCpMonthIpsMap, companyPlanIpsMap, tsDeparts, departId);
						System.out.println("计算页面柱状图数据完毕");
					}
				}
				long en = System.currentTimeMillis();
				System.out.println("计算完毕，用时："+(en - be) / 1000 +"." + (en - be) % 1000 +"秒--------------");
			}
			if(null != j){
				j.setSuccess(success);
				j.setMsg(message);
			}
		} catch (Exception e) {
			e.printStackTrace();
			j.setSuccess(false);
			j.setMsg("计算失败");
		}
	}
	
	
	
	/**
	 * @author xiul
	 * 2016-12-1 下午9:16:51
	 * Description 排序 
	 */
	private void sortCpMonthMap(Map<String, Map<Integer, List<CbsEsPlanIpEntity>>> initCpMonthIpsMap, Map<String ,Map<Integer, List<Cinema>>> cpMonthCmMap){
		try {
			/**
			 * 整理 巡检单位对应月份 对应设备，按照从大到小排序
			 */
			for(String company : initCpMonthIpsMap.keySet()){
				Map<Integer, List<CbsEsPlanIpEntity>> initMonthIpsMap = initCpMonthIpsMap.get(company);
				for(Integer month : initMonthIpsMap.keySet()){
					List<CbsEsPlanIpEntity> cmIdList = initMonthIpsMap.get(month);
					Map<String, Integer> cmCount = new HashMap<String, Integer>();
					//影院：是否可调整
					Map<String, Boolean> cmIsAdjustment = new HashMap<String,Boolean>();
					String cmId = "";
					for(CbsEsPlanIpEntity cip : cmIdList){
						cmId = cip.getCinemaId();
						if(cmIsAdjustment.get(cmId) == null || cmIsAdjustment.get(cmId)){//只有当 影院没有设置过是否可调整，或者 影院可以调整才可以重新判断
							cmIsAdjustment.put(cmId, cip.getIsAdjustment().equals("1") ? true : false);
						}
						if(cmCount.get(cmId) != null){
							cmCount.put(cmId, cmCount.get(cmId) + 1);
						}else{
							cmCount.put(cmId, 1);
						}
					}
					//循环完当前巡检单位 当前月份的所有设备后。。。
					if(null == cpMonthCmMap.get(company)){
						cpMonthCmMap.put(company,new HashMap<Integer, List<Cinema>>());
					}else{
						if(null == cpMonthCmMap.get(company).get(month)){
							cpMonthCmMap.get(company).put(month,new ArrayList<Cinema>());
						}else{
							cpMonthCmMap.get(company).get(month).clear();
						}
					}
					List<Map.Entry<String, Integer>> cmIdListAfter = new ArrayList<Map.Entry<String,Integer>>(cmCount.entrySet());
					//通过Collections.sort(List I,Comparator c)方法进行排序
					Collections.sort(cmIdListAfter,new Comparator<Map.Entry<String, Integer>>() {
	            		@Override
	            		public int compare(Entry<String, Integer> o1, Entry<String, Integer> o2) {
	            			return (o2.getValue() - o1.getValue());
	            		}
	                });
	                for (Entry<String, Integer> entry : cmIdListAfter) {
	                	cpMonthCmMap.get(company).get(month).add(new Cinema(entry.getKey(),cmIsAdjustment.get(entry.getKey())));
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * @author xiul
	 * 2016-10-19 上午10:45:41
	 * Description 初始化的一个 巡检单位 对应巡检月份 对应巡检数量的map（用来存储）
	 */
	public Map<String, Map<Integer, List<CbsEsPlanIpEntity>>> initCompanyMonthPro(List<TSDepart> tsDeparts){
		Map<String, Map<Integer, List<CbsEsPlanIpEntity>>> initCpMonthIpsMap = new HashMap<String, Map<Integer,List<CbsEsPlanIpEntity>>>();//初始化的一个 巡检单位 对应巡检月份 对应巡检数量的map（用来存储）
		for (TSDepart tsDepart : tsDeparts) {
			Map<Integer, List<CbsEsPlanIpEntity>> initMonthPlans = new HashMap<Integer,List<CbsEsPlanIpEntity>>();
			for (int m = 1; m < 13; m++) {
				initMonthPlans.put(m, new ArrayList<CbsEsPlanIpEntity>());
			}
			initCpMonthIpsMap.put(tsDepart.getId(), initMonthPlans);
		}
		return initCpMonthIpsMap;
	}
	
	/**
	 * @author xiul
	 * 2016-10-19 上午10:59:25
	 * Description 计算堆叠柱状图数据 
	 * @param j 传递数据用
	 * @param initCpMonthIpsMap cbsEsPlanEntity表中现有数据
	 * @param companyPlanIpsMap 巡检单位产能表 现有数据 中 巡检单位每个月的计划产能
	 * @param tsDeparts 组织机构中所有第三方机构
	 * @param departId 需要过滤的 机构id
	 */
	private void calcHighchartData(AjaxJson j, Map<String, Map<Integer, List<CbsEsPlanIpEntity>>> initCpMonthIpsMap, Map<String, Map<Integer, Integer>> companyPlanIpsMap, List<TSDepart> tsDeparts, String departId){
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<Highchart> hgs = new ArrayList<Highchart>();
		Map<String, Integer> cpTotalMap = new HashMap<String, Integer>();
		String stack = UUID.randomUUID().toString().replace("-", "");
		String stack2 = UUID.randomUUID().toString().replace("-", "");
		List<Integer> companyCipInMonth = new ArrayList<Integer>();//cbsEsPlan表中数据
		List<Integer> companyProInMonth = new ArrayList<Integer>();//现有巡检机构的计划产能
		for(String company : initCpMonthIpsMap.keySet()){
			if(StringUtil.isNotEmpty(departId)){
				if(!company.equals(departId)){
					continue;
				}
			}
			
			Map<Integer, List<CbsEsPlanIpEntity>> initCpMonthMap = initCpMonthIpsMap.get(company);
			int cpTotal = 0;
			for (int m = 1; m < 13; m++) {//累加每个公司相同月份的数据
				cpTotal += initCpMonthMap.get(m).size();
				if(companyCipInMonth.size() < m){
					companyCipInMonth.add(0);
				}
				if(companyProInMonth.size() < m){
					companyProInMonth.add(0);
				}
				companyCipInMonth.set((m - 1), companyCipInMonth.get((m - 1)) + initCpMonthMap.get(m).size());
				if(null != companyPlanIpsMap.get(company)){
					companyProInMonth.set((m - 1),companyProInMonth.get((m - 1)) + companyPlanIpsMap.get(company).get(m));
				}
			}
			for(TSDepart tsDepart : tsDeparts){
				if(tsDepart.getId().equals(company)){
					cpTotalMap.put(tsDepart.getShortname(), cpTotal);
					break;
				}
			}
		}
		Highchart hgPro = new Highchart();
		hgPro.setStack(stack);
		hgPro.setName("计划产能");
		hgPro.setData(companyProInMonth);
		hgs.add(hgPro);
		
		Highchart hgCip = new Highchart();
		hgCip.setStack(stack2);
		hgCip.setName("巡检计划");
		hgCip.setData(companyCipInMonth);
		hgs.add(hgCip);
		
		dataMap.put("cpTotalMap", cpTotalMap);
		dataMap.put("hgs", hgs);
		j.setAttributes(dataMap);
	}
	
	private void compare(Map<String, Map<Integer, List<CbsEsPlanIpEntity>>> initCpMonthIpsMap, String company, int m,Integer companyPlanCapacity, Map<String, Map<Integer, List<Cinema>>> cpMonthCmMap, int monthIndex) {
		//key 巡检单位 value 每个月的计划巡检量
//		System.out.println("单位：" + company + "\t月份：" + m + "\t影院增长序号：" + monthIndex + "\t本月实际：" + initCpMonthIpsMap.get(company).get(m).size() +"\t产能：" + companyPlanCapacity);
		if(initCpMonthIpsMap.get(company).get(m).size() > companyPlanCapacity){//如果这个巡检单位这月的巡检量超出了这个单位这个月的计划产能
			Iterator<CbsEsPlanIpEntity> cipItr = initCpMonthIpsMap.get(company).get(m).iterator();
			if(null != cpMonthCmMap.get(company).get(m) && cpMonthCmMap.get(company).get(m).size() > 0){//如果巡检单位对应这个月份的影院数量不为0 或者不为null
				while(cipItr.hasNext()){
					CbsEsPlanIpEntity cip = cipItr.next();
					if(cpMonthCmMap.get(company).get(m).get(monthIndex).getIsAdjustment() && cip.getCanMove() && //当前影院可调整(包含2种情况)
							cip.getCinemaId().equals(cpMonthCmMap.get(company).get(m).get(monthIndex).getCinemaId())){//优先调整当前巡检单位当前月份设备多的影院
						cip.setIpMonth(m + 1);//改变月份
						initCpMonthIpsMap.get(company).get(m + 1).add(cip);//把当前巡检单位当前影院当前月份的这个设备向后挪一个月
						cipItr.remove();
//						System.out.println("\t本月实际：" + initCpMonthIpsMap.get(company).get(m).size() +"\t下个月：" + initCpMonthIpsMap.get(company).get(m + 1).size() + "\t移出设备ID：" + cip.getEquipmentSpare().getId() + "当前影院 " + cpMonthCmMap.get(company).get(m).get(monthIndex).getCinemaId() + " 是否可以移动： " + cpMonthCmMap.get(company).get(m).get(monthIndex).getIsAdjustment());
					}
				}
				if(monthIndex != cpMonthCmMap.get(company).get(m).size() - 1){
					monthIndex++;
					compare(initCpMonthIpsMap,company,m,companyPlanCapacity,cpMonthCmMap,monthIndex);
				}
			}
		}
	}

	@Override
	public void queryDatasForDatagridOfView(HttpServletRequest request, DataGrid dataGrid) {
		Map<String, String[]> paramsMap = request.getParameterMap();
		String generateSource = request.getParameter("source");
		String departId = request.getParameter("departId");

		Integer year = oConvertUtils.getInt(request.getParameter("year"));//实时或自动查询时的年份
		
		List<Map<String, Object>> rsMapList = new ArrayList<Map<String,Object>>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	cip.cinema_id AS id, ");
		sql.append(" 	cm.`name` AS cmName, ");
		sql.append(" 	COUNT(cip.id) AS ipCount, ");
		sql.append(" 	cip.ip_year AS ipYear, cip.ip_month AS ipMonth, ");
		sql.append(" 	CONCAT(cip.ip_year,'-',IF(cip.ip_month < 10,CONCAT('0',cip.ip_month),cip.ip_month)) AS ipDate, ");
		sql.append(" 	MIN(es.repair_end_date) AS repairEndDate, ");
		sql.append(" 	dp.id AS ipCompany, ");
		sql.append(" 	dp.departname AS ipCompanyName ");
		sql.append(" FROM ");
		sql.append(" 	cbs_es_plan_ip cip ");
		sql.append(" LEFT JOIN zy_cinema cm ON cm.id = cip.cinema_id ");
		sql.append(" LEFT JOIN t_s_depart dp ON dp.id = cip.ip_company_id ");
		sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = cip.es_id ");
		sql.append("	WHERE ");
		sql.append(" cip.ip_year = "+ year +" ");
		sql.append(" AND cip.generate_source ='").append(generateSource).append("' ");//固有条件
		if(StringUtil.isNotEmpty(departId)){
			sql.append(" AND cip.ip_company_id ='").append(departId).append("' ");//固有条件
		}
		String sortName = dataGrid.getSort();
		SortDirection order = dataGrid.getOrder();
		
		StringBuffer  groupOrderSql = new StringBuffer();
		groupOrderSql.append(" GROUP BY ");
		groupOrderSql.append(" 	cip.cinema_id, ipMonth, ipCompany ");
		groupOrderSql.append(" ORDER BY ");
		if(StringUtil.isNotEmpty(sortName) && StringUtil.isNotEmpty(order)){
			if(sortName.equals("ipDate")){
				sortName = "ipMonth";
			}
			groupOrderSql.append(sortName + " " + order);
		}else{
			groupOrderSql.append(" 	cmName ");
		}
		StringBuffer conditionSql = new StringBuffer();
		int totalCount = 0;
		Integer page = 1,rows = 10;
		rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
		page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
		if(StringUtil.isNotEmpty(paramsMap.get("cmName")) && StringUtil.isNotEmpty(paramsMap.get("cmName")[0])){
			conditionSql.append(" AND cm.name ='").append(paramsMap.get("cmName")[0].toString()).append("' ");
		}
		if(StringUtil.isNotEmpty(paramsMap.get("ipMonth")) && StringUtil.isNotEmpty(paramsMap.get("ipMonth")[0])){
			conditionSql.append(" AND cip.ip_month ='").append(paramsMap.get("ipMonth")[0].toString()).append("' ");
		}
		if(StringUtil.isNotEmpty(paramsMap.get("ipCompanyName")) && StringUtil.isNotEmpty(paramsMap.get("ipCompanyName")[0])){
			conditionSql.append(" AND dp.shortname ='").append(paramsMap.get("ipCompanyName")[0].toString()).append("' ");
		}
		if(rows == 0 || page == 0){
			rsMapList = findForJdbc(sql.toString() + conditionSql.toString() + groupOrderSql.toString());
		}else{
			dataGrid.setPage(page);
			dataGrid.setRows(rows);
			rsMapList = findForJdbc(sql.toString() + conditionSql.toString() + groupOrderSql.toString(), page, rows);
		}
		List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString() + conditionSql.toString() + groupOrderSql.toString());
		if(null != countMapList && countMapList.size() > 0){
			totalCount = countMapList.size();
		}
		dataGrid.setPage(page);
		dataGrid.setRows(rows);
		dataGrid.setTotal(totalCount);
		dataGrid.setResults(rsMapList);
		
	}

	@Override
	public void getCipChartData(AjaxJson j,HttpServletRequest request) {
		String departId = oConvertUtils.getString(request.getParameter("departId"));
		String source = oConvertUtils.getString(request.getParameter("source"));
		String message ="";
		boolean success = true;
		try {
			Calendar cal = Calendar.getInstance();
			int nextYear = 0;
			if(source.equals(StaticValue.DIC_GENERATE_SOURCE_REAL_TIME)){
				nextYear = cal.get(Calendar.YEAR);//当年
			}else if(source.equals(StaticValue.DIC_GENERATE_SOURCE_AUTO)){
				nextYear = cal.get(Calendar.YEAR) + 1;//下一年
			}
			j.setObj(nextYear);
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT ");
			sql.append(" 	plan_capacity AS planCapacity, MONTH(years) AS companyMonth, service_provider AS ipCompany");
			sql.append(" FROM ");
			sql.append(" 	cbs_service_capacity ");
			sql.append(" WHERE ");
			sql.append(" 	YEAR(years) = ").append(nextYear).append(" ");
			sql.append(" GROUP BY ");
			sql.append(" 	service_provider, ");
			sql.append(" 	years ");
			sql.append(" ORDER BY ");
			sql.append(" 	service_provider, ");
			sql.append(" 	years ");
			List<Map<String, Object>> serviceCapacityMapList = super.findForJdbc(sql.toString());
			
			if(null != serviceCapacityMapList && serviceCapacityMapList.size() > 0){
				Map<String, Map<Integer, Integer>> companyPlanIpsMap = new HashMap<String, Map<Integer,Integer>>();//巡检单位产能表 现有数据 中 巡检单位每个月的计划产能
				for (Map<String, Object> companyMap : serviceCapacityMapList) {
					String currCompany = companyMap.get("ipCompany").toString();
					if(null == companyPlanIpsMap.get(currCompany)){
						companyPlanIpsMap.put(currCompany,new HashMap<Integer, Integer>());
					}
					companyPlanIpsMap.get(currCompany).put(Integer.valueOf(companyMap.get("companyMonth").toString()), Integer.valueOf(companyMap.get("planCapacity").toString()));
				}
				
				List<TSDepart> tsDeparts = super.findHql("from TSDepart where orgType = ?", StaticValue.DIC_ORG_TYPE_THIRD_PARTY);
				//初始化一个巡检单位每个月的计划产能
				Map<String, Map<Integer, List<CbsEsPlanIpEntity>>> initCpMonthIpsMap = initCompanyMonthPro(tsDeparts);
				sql = new StringBuffer();
				sql.append(" SELECT ");
				sql.append(" 	cip.ip_company_id AS ipCompany, cip.ip_month AS ipMonth ");
				sql.append(" FROM ");
				sql.append(" 	cbs_es_plan_ip cip ");
				sql.append(" WHERE cip.generate_source = '").append(source).append("' ");
				sql.append(" AND cip.ip_year =").append(nextYear).append(" ");
				List<Map<String, Object>> cipsMapList = super.findForJdbc(sql.toString());
				for(Map<String, Object> cipMap : cipsMapList){
					initCpMonthIpsMap.get(cipMap.get("ipCompany").toString()).get(Integer.valueOf(cipMap.get("ipMonth").toString())).add(new CbsEsPlanIpEntity());
				}
				calcHighchartData(j, initCpMonthIpsMap, companyPlanIpsMap, tsDeparts, departId);
			}else{
				message = "巡检服务商没有设置["+nextYear+"年]产能";
				success = false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			success = false;
			message = "图标数据查询异常";
		}
		
		j.setSuccess(success);
		j.setMsg(message);
		
	}

	@Override
	public void queryViewDetialData(HttpServletRequest request, DataGrid dataGrid) {
		Map<String,String[]> paramsMap = request.getParameterMap();
		String cmId = oConvertUtils.getString(request.getParameter("cmId"));
		String ipYear = oConvertUtils.getString(request.getParameter("ipYear"));
		String ipMonth = oConvertUtils.getString(request.getParameter("ipMonth"));
		String source = oConvertUtils.getString(request.getParameter("source"));
		String departId = oConvertUtils.getString(request.getParameter("departId"));
		if(!cmId.equals("") && !ipMonth.equals("")){
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT ");
			sql.append(" 	cip.id, es.sn, et.type_desc AS typeDesc, CONCAT(cip.ip_year,'-',cip.ip_month) AS cipYearMonth, ");
			sql.append(" 	es.bp_no AS bpNo, DATE_FORMAT(es.repair_end_date,'%Y-%m-%d') AS repairEndDate ");
			sql.append(" FROM ");
			sql.append(" 	cbs_es_plan_ip cip ");
			sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = cip.es_id ");
			sql.append(" LEFT JOIN zy_equipment_type et ON et.id = es.type ");
			sql.append(" WHERE ");
			sql.append(" 	cip.generate_source = '").append(source).append("' ");//固有条件
			if(StringUtil.isNotEmpty(departId)){
				sql.append(" AND cip.ip_company_id ='").append(departId).append("' ");//固有条件
			}
			StringBuffer conditionSql = new StringBuffer();
			conditionSql.append(" 	AND cip.cinema_id = '").append(cmId).append("' ");
			conditionSql.append(" 	AND cip.ip_year = '").append(ipYear).append("' ");
			conditionSql.append(" 	AND cip.ip_month = '").append(ipMonth).append("' ");
			int totalCount = 0;
			Integer page = 1,rows = 10;
			rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
			page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
			List<Map<String, Object>> rsMapList = new ArrayList<Map<String,Object>>();
			if(rows == 0 && page == 0){
				rsMapList = findForJdbc(sql.toString() + conditionSql.toString());
			}else{
				dataGrid.setPage(page);
				dataGrid.setRows(rows);
				rsMapList = findForJdbc(sql.toString() + conditionSql.toString(), page, rows);
			}
			List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
			if(null != countMapList && countMapList.size() > 0){
				totalCount = countMapList.size();
			}
			dataGrid.setTotal(totalCount);
			dataGrid.setResults(rsMapList);
		}
	}

	@Override
	public void getUpdateData(HttpServletRequest request) {
		String cmId = oConvertUtils.getString(request.getParameter("cmId"));
		String source = oConvertUtils.getString(request.getParameter("source"));
		String ipCompany = oConvertUtils.getString(request.getParameter("ipCompany"));
		String ipYear = oConvertUtils.getString(request.getParameter("ipYear"));
		String ipMonth = oConvertUtils.getString(request.getParameter("ipMonth"));
		
		String ipCount = oConvertUtils.getString(request.getParameter("ipCount"));
		String cmName = oConvertUtils.getString(request.getParameter("cmName"));
		
		ZyCinemaEntity zyCm = super.get(ZyCinemaEntity.class, cmId);
		
		String stack = UUID.randomUUID().toString().replace("-", "");
		String stack2 = UUID.randomUUID().toString().replace("-", "");
		List<Integer> companyCscInMonth = new ArrayList<Integer>();//现有巡检机构的计划产能
		List<Integer> companyCipInMonth = new ArrayList<Integer>();//cbsEsPlan表中数据
		List<Highchart> hgs = new ArrayList<Highchart>();
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	ip_month AS ipMonth ,COUNT(id) AS monthTotal ");
		sql.append(" FROM ");
		sql.append(" 	cbs_es_plan_ip cip ");
		sql.append(" WHERE ");
//		sql.append(" 	cinema_id = '").append(cmId).append("' ");
//		sql.append(" 	AND ip_month = '").append(ipMonth).append("' ");
		sql.append(" 	cip.ip_company_id = '").append(ipCompany).append("' ");
		sql.append(" 	AND cip.ip_year = '").append(ipYear).append("' ");
		sql.append(" 	AND cip.generate_source = '").append(source).append("' ");
		sql.append(" GROUP BY ");
		sql.append(" 	ip_month ");
		sql.append(" ORDER BY ");
		sql.append("	ip_month ");
		List<Map<String, Object>> mapList = super.findForJdbc(sql.toString());
		if(null != mapList){
			for(int m = 1;m < 13;m++){
				boolean thisMonthHad = false;
				for(Map<String, Object> map : mapList){
					if(Integer.valueOf(map.get("ipMonth").toString()) == m){
						companyCipInMonth.add(Integer.valueOf(map.get("monthTotal").toString()));
						thisMonthHad = true;
						break;
					}
				}
				if(!thisMonthHad){
					companyCipInMonth.add(0);
				}
			}
		}
		sql = new StringBuffer();//查看当前影院的每个月份设备的可调节性
		sql.append(" SELECT ");
		sql.append(" 	is_adjustment AS isAdjustment, ");
		sql.append(" 	ip_month AS ipMonth ");
		sql.append(" FROM ");
		sql.append(" 	cbs_es_plan_ip cip ");
		sql.append(" WHERE ");
		sql.append(" 	cinema_id = '").append(cmId).append("' ");
		sql.append(" 	AND ip_month = '").append(ipMonth).append("' ");
		sql.append(" 	AND cip.generate_source = '").append(source).append("' ");
		sql.append(" ORDER BY ");
		sql.append(" 	ip_month ");
		List<Map<String, Object>> cmIsAdjustmentMapList = super.findForJdbc(sql.toString());
		Map<String, Boolean> cmMonthIsAdjustmentMap = new HashMap<String, Boolean>();
		for(Map<String, Object> map : cmIsAdjustmentMapList){
			if(null != cmMonthIsAdjustmentMap.get(map.get("ipMonth").toString())
					&& !cmMonthIsAdjustmentMap.get(map.get("ipMonth").toString())){
				continue;
			}
			if(map.get("isAdjustment").toString().equals("0")){
				cmMonthIsAdjustmentMap.put(map.get("ipMonth").toString(), false);
			}else{
				cmMonthIsAdjustmentMap.put(map.get("ipMonth").toString(), true);
			}
		}
		
		Calendar cal = Calendar.getInstance();
		
		sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	MONTH(years) AS spMonth, plan_capacity AS planCapacity ");
		sql.append(" FROM ");
		sql.append(" 	cbs_service_capacity ");
		sql.append(" WHERE ");
		sql.append(" 	service_provider = '").append(ipCompany).append("' ");
		sql.append(" 	AND YEAR(years) = ").append(cal.get(Calendar.YEAR) + 1).append(" ");
		List<Map<String, Object>> cscsMapList = super.findForJdbc(sql.toString());
		if(null != cscsMapList){
			for(int m = 1;m < 13;m++){
				boolean thisMonthHad = false;
				for(Map<String, Object> map : cscsMapList){
					if(Integer.valueOf(map.get("spMonth").toString()) == m){
						companyCscInMonth.add(Integer.valueOf(map.get("planCapacity").toString()));
						thisMonthHad = true;
						break;
					}
				}
				if(!thisMonthHad){
					companyCscInMonth.add(0);
				}
			}
		}
		
		Highchart hgPro = new Highchart();
		hgPro.setStack(stack);
		hgPro.setName("计划产能");
		hgPro.setData(companyCscInMonth);
		hgs.add(hgPro);
		
		Highchart hgCip = new Highchart();
		hgCip.setStack(stack2);
		hgCip.setName("巡检计划");
		hgCip.setData(companyCipInMonth);
		hgs.add(hgCip);
		List<TSDepart> tpTsDeparts = super.findHql("from TSDepart where orgType = ?", StaticValue.DIC_ORG_TYPE_THIRD_PARTY);
		
		request.setAttribute("hgs", JSONObject.toJSONString(hgs));
		request.setAttribute("tpTsDeparts", tpTsDeparts);
		request.setAttribute("cmMonthIsAdjustmentMap", JSONObject.toJSONString(cmMonthIsAdjustmentMap));
		request.setAttribute("cmId",cmId);
		request.setAttribute("cmName",cmName);
		request.setAttribute("zyCm",zyCm);
		request.setAttribute("ipMonth",ipMonth);
		request.setAttribute("ipCount",ipCount);
		request.setAttribute("ipCompany", ipCompany);
		request.setAttribute("source", source);
	}

	@Override
	public void updateCip(AjaxJson j, HttpServletRequest request) {
		String message = "";
		boolean success = true;
		String cmId = oConvertUtils.getString(request.getParameter("cmId"));
		String source = oConvertUtils.getString(request.getParameter("source"));
		String ipCompany = oConvertUtils.getString(request.getParameter("ipCompany"));
		String ipMonth = oConvertUtils.getString(request.getParameter("ipMonth"));
		
		String cIpCompany = oConvertUtils.getString(request.getParameter("cIpCompany"));
		String cIpMonth = oConvertUtils.getString(request.getParameter("cIpMonth"));
		
		StringBuffer updateSql = new StringBuffer();
		updateSql.append(" UPDATE ");
		updateSql.append("	cbs_es_plan_ip ");
		updateSql.append(" SET ");
		updateSql.append("	ip_company_id = '").append(cIpCompany).append("', ");
		updateSql.append("	ip_month = '").append(cIpMonth).append("' ");
		updateSql.append(" WHERE ");
		updateSql.append("	cinema_id='").append(cmId).append("' ");
		updateSql.append("	AND generate_source='").append(source).append("' ");
		updateSql.append("	AND ip_company_id='").append(ipCompany).append("' ");
		updateSql.append("	AND ip_month='").append(ipMonth).append("' ");
		super.executeSql(updateSql.toString());
		if(null != j){
			j.setMsg(message);
			j.setSuccess(success);
		}
	}

	@Override
	public void genarateIp(AjaxJson j, HttpServletRequest request) {
		boolean success = true;
		String message = "生成成功";
		String generateSource = oConvertUtils.getString(request.getParameter("generateSource"));
		String ipYear = oConvertUtils.getString(request.getParameter("ipYear"));
//		List<CbsEsPlanIpEntity> cips = super.findByProperty(CbsEsPlanIpEntity.class, "generateSource", generateSource);
		List<CbsEsPlanIpEntity> cips = super.findHql("from CbsEsPlanIpEntity where generateSource = ? AND ip_year = ?", generateSource, ipYear);
		String prRemark = "自动生成巡检计划";
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	dp.id AS dpId, dp.org_code AS orgCode, ct.id AS ctId ");
		sql.append(" FROM ");
		sql.append(" 	t_s_base_user bu ");
		sql.append(" LEFT JOIN zy_contacts ct ON ct.base_id = bu.ID ");
		sql.append(" LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID ");
		sql.append(" LEFT JOIN t_s_depart dp ON dp.ID = uo.org_id ");
		sql.append(" LEFT JOIN t_s_role_user ru ON ru.userid = bu.ID ");
		sql.append(" LEFT JOIN t_s_role ro ON ro.ID = ru.roleid ");
		sql.append(" WHERE ");
		sql.append(" 	ro.rolecode = '").append(StaticValue.RULE_CODE_THIRDPARTY_HEADER).append("' ");
		sql.append(" AND ct.is_delete = '0' ");
		sql.append(" AND ct.state = '").append(StaticValue.DIC_ENGINEER_STATE_ONJOB).append("' ");
		sql.append(" AND ct.position = '").append(StaticValue.DIC_CONTACT_POSITION_TP_SERVER_BUSINESSR).append("' ");
		sql.append(" AND dp.bus_scope LIKE '%").append(StaticValue.DIC_BUS_SCOPE_INSPECTION).append("%' ");
		sql.append(" AND dp.org_type = '").append(StaticValue.DIC_ORG_TYPE_THIRD_PARTY).append("' ");
		sql.append(" AND dp.logic_delete = 0 ");
		List<Map<String, Object>> tpIpCtsMapList = super.findForJdbc(sql.toString());
		
		Map<String, Map<String, String>> tpCtMap = new HashMap<String, Map<String, String>>();//第三方巡检对应的联系人
		for(Map<String, Object> map : tpIpCtsMapList){
			if(null == tpCtMap.get(map.get("dpId").toString())){
				Map<String, String> m = new HashMap<String, String>();
				m.put("ctId", map.get("ctId").toString());
				m.put("orgCode", map.get("orgCode").toString());
				tpCtMap.put(map.get("dpId").toString(),m);
			}
		}
		
		List<ZyInspectionPlanEntity> ips = new ArrayList<ZyInspectionPlanEntity>();
		List<ZyEquipmentSpareEntity> ess = new ArrayList<ZyEquipmentSpareEntity>();
        Map<String, Object> planIdMap = super.findOneForJdbc("SELECT plan_id AS planId FROM zy_inspection_plan WHERE ISNULL(parent_ip_id) ORDER BY plan_id DESC LIMIT 0,1");
        try {
	        boolean first = true;
	        String lastPlanId = "";
	        System.out.println(cips.size() + "条待生成数据");
	        int count = 0;
			for (CbsEsPlanIpEntity cip : cips) {
				System.out.println("计算第" + ++count +"条...");
				ZyInspectionPlanEntity zyIP = new ZyInspectionPlanEntity();
				if(null == tpCtMap.get(cip.getIpCompanyId())){
					success = false;
					TSDepart depart = super.getEntity(TSDepart.class, cip.getIpCompanyId());
					message = "["+ depart.getDepartname() + "]没有设置巡检联系人";
					break;
				}else if(StringUtil.isEmpty(tpCtMap.get(cip.getIpCompanyId()).get("orgCode"))){
					success = false;
					TSDepart depart = super.getEntity(TSDepart.class, cip.getIpCompanyId());
					message = "["+ depart.getDepartname() + "]没有设置BP号";
					break;
				}
				zyIP.setCompany(tpCtMap.get(cip.getIpCompanyId()).get("orgCode"));
				Date ipDate = DateUtils.str2Date(cip.getIpYear() + "-" + cip.getIpMonth() + "-01", DateUtils.date_sdf);
				zyIP.setIpPlanDate(ipDate);
				ZyContactsEntity tpIpCt = new ZyContactsEntity();
				tpIpCt.setId(tpCtMap.get(cip.getIpCompanyId()).get("ctId"));
				zyIP.setContacter(tpIpCt);
				ZyEquipmentSpareEntity es = cip.getEquipmentSpare();
				ess.add(es);
				//生成巡检计划ID
				String cipYearLastTwo = cip.getIpYear().toString().substring(2);
				if(first){
					if(null != planIdMap && StringUtil.isNotEmpty(planIdMap.get("planId"))){//如果已经有巡检计划存在
						String currPlanMaxYear = planIdMap.get("planId").toString().substring(2,4);
						if(Integer.valueOf(currPlanMaxYear) < Integer.valueOf(cipYearLastTwo)){//如果当前存在前一年的计划，但是生成的却是下一年的计划，需要从头开始 00001
							zyIP.setPlanId(generatePlanId(cipYearLastTwo,""));
						}else{
							zyIP.setPlanId(generatePlanId(cipYearLastTwo,planIdMap.get("planId").toString()));
						}
					}else{
						zyIP.setPlanId(generatePlanId(cipYearLastTwo,""));
					}
				}else{
					zyIP.setPlanId(generatePlanId(cipYearLastTwo,lastPlanId));
				}
				lastPlanId = zyIP.getPlanId();
				//关联设备
				zyIP.setEquipmentSpare(es);
				zyIP.setPlanQuarter(StaticValue.DIC_PLAN_INSPECTION_QUARTER_ONE);//默认给 第一季度
				//设置巡检计划状态
				zyIP.setState(StaticValue.DIC_PLAN_STATE_TODO);
				//初始化 工程师修改预约上门时间次数 0
				zyIP.setEngineerOrderTimeCount(0);
				zyIP.setLightPath(StaticValue.DIC_LIGHT_PATH_01);//默认光路1
				//设置巡检单位是否有区域工程师，为后续首页显示代办以及其他地方方便判断取值
				zyIP.setCompanyHadAreaEg(zyCommonService.judgeCompanyHadAreaEnginee(zyIP.getCompany(), zyIP.getLightPath()) ? "1" : "0");
				zyIP.setRemarks(prRemark);
				zyIP.setGenerateSource(generateSource);//设置生成方式为 页面传值（自动 or 实时新增）
				zyIP.setCanUpdateOrdertime("1");//是否可以修改巡检年月默认1（微信用）
				ips.add(zyIP);
		
				first = false;
			}
			if(success){
				System.out.println("开始保存生成数据...");
				super.batchSave(ips);
				System.out.println("保存生成数据完毕");
				for(ZyEquipmentSpareEntity es : ess){
					//改变设备 为 有巡检计划
					es.setIsHadInspection("1");
					//剩余巡检次数 减少1
					Integer residualIpCount = es.getResidualIpCount() == null ? 0 : es.getResidualIpCount();
					residualIpCount = residualIpCount != 0 ? --residualIpCount : 0 ;
					es.setResidualIpCount(residualIpCount);
				}
				System.out.println("开始修改设备，是否有巡检计划 列...");
				super.batchUpdate(ess);//更新设备
				System.out.println("修改设备 完毕");
				List<ZyPlanRecordEntity> prs = new ArrayList<ZyPlanRecordEntity>();
				for(ZyInspectionPlanEntity zyIP : ips){
					//保存巡检计划流程记录
					ZyPlanRecordEntity zyPlanRecord = new ZyPlanRecordEntity();
					zyPlanRecord.setBusId(zyIP.getId());
					zyPlanRecord.setPlanState(zyIP.getState());
					zyPlanRecord.setRemark(prRemark);
					prs.add(zyPlanRecord);
				}
				System.out.println("开始保存巡检记录...");
				super.batchSave(prs);
				System.out.println("保存巡检记录完毕");
			}
			j.setSuccess(success);
			j.setMsg(message);
        } catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private String generatePlanId(String year,String currPlanId){
		StringBuffer sb = new StringBuffer();
        sb.append("PM").append(year);
        String partThree;
        if (!currPlanId.equals("")) {
            String currMaxId = currPlanId.substring(4);
            Long currMaxIdLong = Long.valueOf(currMaxId) + 1;
            if (currMaxIdLong >= 1 && currMaxIdLong <= 9) {
                sb.append("00000").append(currMaxIdLong);
            } else if (currMaxIdLong >= 10 && currMaxIdLong <= 99) {
                sb.append("0000").append(currMaxIdLong);
            } else if (currMaxIdLong >= 100 && currMaxIdLong <= 999) {
                sb.append("000").append(currMaxIdLong);
            } else if (currMaxIdLong >= 1000 && currMaxIdLong <= 9999) {
                sb.append("00").append(currMaxIdLong);
            } else if (currMaxIdLong >= 10000 && currMaxIdLong <= 99999) {
                sb.append("0").append(currMaxIdLong);
            } else if (currMaxIdLong >= 10000 && currMaxIdLong <= 999999) {
                sb.append(currMaxIdLong);
            }
        } else {
            //初始化
            partThree = "000001";
            sb.append(partThree);
        }
        return sb.toString();
	}

	@Override
	public List<Highchart> queryHomePageEsIpHighchartData(HttpServletRequest request) {
		List<Highchart> highcharts = new ArrayList<Highchart>();
		Calendar cal = Calendar.getInstance();
		int currYear = cal.get(Calendar.YEAR);
		List<Map<String, Object>> mapList = super.findForJdbc("SELECT COUNT(*) AS total, generate_source AS generateSource FROM zy_inspection_plan WHERE YEAR (ip_plan_date) = ? GROUP BY generate_source" , currYear);
		Map<String, Object> planCapacityMap = super.findOneForJdbc("SELECT SUM(plan_capacity) AS sumCount FROM cbs_service_capacity WHERE YEAR (years) = ?" , currYear);
		List<Integer> autoList = new ArrayList<Integer>();
		List<Integer> manualList = new ArrayList<Integer>();
		List<Integer> realTimeList = new ArrayList<Integer>();
		if(null != mapList && mapList.size() > 0){
			for(Map<String, Object> map : mapList){
				if(!StringUtil.isNotEmpty(map.get("generateSource"))){
					continue;
				}
				String generateSource = map.get("generateSource").toString();
				int count = Integer.valueOf(map.get("total").toString());
				if(generateSource.equals(StaticValue.DIC_GENERATE_SOURCE_AUTO)){
					autoList.add(count);
				}else if(generateSource.equals(StaticValue.DIC_GENERATE_SOURCE_MANUAL)){
					manualList.add(count);
				}else if(generateSource.equals(StaticValue.DIC_GENERATE_SOURCE_REAL_TIME)){
					realTimeList.add(count);
				}
			}
		}else{
			autoList.add(0);
			manualList.add(0);
			realTimeList.add(0);
		}
		Highchart autoHg = new Highchart();
		autoHg.setData(autoList);
		autoHg.setName("年度自动创建");
		autoHg.setStack("statck1");
		
		Highchart maualHg = new Highchart();
		maualHg.setData(realTimeList);
		maualHg.setName("实时自动创建");
		maualHg.setStack("statck1");
		
		Highchart realTimeHg = new Highchart();
		realTimeHg.setData(manualList);
		realTimeHg.setName("手动创建");
		realTimeHg.setStack("statck1");
		
		List<Integer> planCapacityList = new ArrayList<Integer>();
		if(null != planCapacityMap && StringUtil.isNotEmpty(planCapacityMap.get("sumCount"))){
			planCapacityList.add(Integer.valueOf(planCapacityMap.get("sumCount").toString()));
		}else{
			planCapacityList.add(0);
		}
		Highchart planCapacityHg = new Highchart();
		planCapacityHg.setData(planCapacityList);
		planCapacityHg.setName("计划产能");
		planCapacityHg.setStack("statck2");
		
		highcharts.add(autoHg);
		highcharts.add(maualHg);
		highcharts.add(realTimeHg);
		highcharts.add(planCapacityHg);
		return highcharts;
	}
	
	@Override
	public void exportCips(HttpServletRequest request, HttpServletResponse response, AjaxJson j) {
		OutputStream out = null;
		try {
			String generateSource = request.getParameter("generateSource");
			String ipYear = request.getParameter("ipYear");
			if(StringUtil.isNotEmpty(generateSource)){
				StringBuffer sql = new StringBuffer();
//				sql.append(" SELECT ");
//				sql.append(" et.type_desc, es.sn, dp.departname, ty_p.territoryname AS province, ty.territoryname AS city, ");
//				sql.append(" cm.`name` AS cmName, cm.address AS cmAddress, ct.`name` AS ctName,  ");
//				sql.append(" ct.cellphone1 AS ctCellphone, ct.fax AS ctFax,  ");
//				sql.append(" ct2.`name` AS ct2Name, ct2.cellphone1 AS ct2Cellphone, ct3.`name`, ct3.cellphone1, ");
//				sql.append(" es.install_date, es.last_year_inspection_date, ");
//				sql.append(" '','','','', ");
//				sql.append(" rm.room_num, rm.screen_type, rm.screen_plus, rm.screen_width, rm.screen_hight, rm.curtain_width, rm.curtain_hight, rm.show_km ");
//				sql.append(" FROM ");
//				sql.append(" cbs_es_plan_ip cip  ");
//				sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = cip.es_id ");
//				sql.append(" LEFT JOIN zy_equipment_type et ON et.id = es.type ");
//				sql.append(" LEFT JOIN zy_room rm ON rm.id = es.house ");
//				sql.append(" LEFT JOIN zy_cinema cm ON cm.id = es.belong_cinema_id ");
//				sql.append(" LEFT JOIN t_s_depart dp ON dp.id = cm.belong_org ");
//				sql.append(" LEFT JOIN t_s_territory ty ON ty.id = cm.province ");
//				sql.append(" LEFT JOIN t_s_territory ty_p ON ty_p.ID = ty.territoryparentid ");
//				sql.append(" LEFT JOIN zy_contacts ct ON ct.belong_cinema = cm.id AND ct.position = '").append(StaticValue.DIC_CONTACT_POSITION_C_MANAGER).append("' ");
//				sql.append(" LEFT JOIN zy_contacts ct2 ON ct2.belong_cinema = cm.id AND ct2.position = '").append(StaticValue.DIC_CONTACT_POSITION_C_TECHNICAL_DIRECTOR).append("' ");
//				sql.append(" LEFT JOIN t_s_depart dp2 on dp2.id = cip.ip_company_id ");
//				sql.append(" LEFT JOIN t_s_user_org uo on uo.org_id = dp2.id");
//				sql.append(" LEFT JOIN zy_contacts ct3 on ct3.base_id = uo.user_id AND ct3.state = '").append(StaticValue.DIC_ENGINEER_STATE_ONJOB).append("' AND ct3.position = '").append(StaticValue.DIC_CONTACT_POSITION_TP_SERVER_BUSINESSR).append("' AND ct3.is_delete = '0' ");
//				sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = uo.user_id ");
//				sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid  AND ro.rolecode = '").append(StaticValue.RULE_CODE_THIRDPARTY_HEADER).append("'");
//				sql.append(" WHERE ");
//				sql.append(" 	cip.generate_source = '").append(generateSource).append("' ");
//				sql.append(" AND !ISNULL(es.id) ");
				
				sql.append(" SELECT ");
				sql.append(" 	cip.ip_company_id, cip.ip_month, et.type_desc, es.sn, es.bp_no, ty_p.territoryname AS province, ty.territoryname AS city, ");
				sql.append(" 	cm.id AS cmId, cm.`name` AS cmName, cm.address AS cmAddress, ");
				sql.append(" 	DATE_FORMAT(es.install_date,'%Y-%m-%d') AS install_date, DATE_FORMAT(es.last_year_inspection_date,'%Y-%m-%d') AS last_year_inspection_date, ");
				sql.append(" 	'', '', '', '', ");
				sql.append(" 	rm.room_num, rm.screen_type, rm.screen_plus, rm.screen_width, rm.screen_hight, rm.curtain_width, rm.curtain_hight, rm.show_km ");
				sql.append(" FROM ");
				sql.append(" 	cbs_es_plan_ip cip  ");
				sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = cip.es_id ");
				sql.append(" LEFT JOIN zy_equipment_type et ON et.id = es.type ");
				sql.append(" LEFT JOIN zy_room rm ON rm.id = es.house ");
				sql.append(" LEFT JOIN zy_cinema cm ON cm.id = es.belong_cinema_id ");
//				sql.append(" LEFT JOIN t_s_depart dp ON dp.id = cm.belong_org ");
				sql.append(" LEFT JOIN t_s_territory ty ON ty.id = cm.province ");
				sql.append(" LEFT JOIN t_s_territory ty_p ON ty_p.ID = ty.territoryparentid ");
				sql.append(" WHERE ");
				sql.append(" 	cip.generate_source = '").append(generateSource).append("' ");
				sql.append(" AND cip.ip_year = '").append(ipYear).append("' ");
				sql.append(" AND ! ISNULL(es.id) ");
				List<Map<String, Object>> cipEsMapList = super.findForJdbc(sql.toString());
				
				sql = new StringBuffer();//影院经理，影院技术负责人。（包含基本条件）
				sql.append(" SELECT ");
				sql.append(" 	ct.belong_cinema, name, cellphone1, fax, position ");
				sql.append(" FROM ");
				sql.append(" 	zy_contacts ct ");
				sql.append(" WHERE ");
				sql.append(" 	ct.state = '").append(StaticValue.DIC_ENGINEER_STATE_ONJOB).append("' ");
				sql.append(" AND ( ");
				sql.append(" 	ct.position = '").append(StaticValue.DIC_CONTACT_POSITION_C_MANAGER).append("' ");
				sql.append(" 	OR ct.position = '").append(StaticValue.DIC_CONTACT_POSITION_C_TECHNICAL_DIRECTOR).append("' ");
				sql.append(" ) ");
				sql.append(" AND ct.is_delete = '0' ");
				sql.append(" AND !ISNULL(belong_cinema) ");
				List<Map<String, Object>> ctsMapList = super.findForJdbc(sql.toString());
				Map<String, Map<String, Object>> ctsMap = new HashMap<String, Map<String,Object>>();
				for(Map<String, Object> map : ctsMapList){
					ctsMap.put(map.get("belong_cinema").toString(), map);
				}
				
				sql = new StringBuffer();//影院巡检联系人。（包含基本条件）
				sql.append(" SELECT ");
				sql.append(" 	ct.belong_cinema, name, cellphone1 ");
				sql.append(" FROM ");
				sql.append(" 	zy_contacts ct ");
				sql.append(" WHERE ");
				sql.append(" 	ct.state = '").append(StaticValue.DIC_ENGINEER_STATE_ONJOB).append("' ");
				sql.append(" AND ct.is_delete = '0' ");
		        sql.append(" AND ct.type = '").append(StaticValue.DIC_CONTACT_TYPE_CINEMA_CONTACT).append("' ");
				sql.append(" AND ct.con_business like '%").append(StaticValue.DIC_BUS_SCOPE_INSPECTION).append("%' ");
				sql.append(" AND !ISNULL(belong_cinema) ");
				List<Map<String, Object>> cmCtMapList = super.findForJdbc(sql.toString());
				Map<String, Map<String, Object>> cmCtMap = new HashMap<String, Map<String,Object>>();
				for(Map<String, Object> map : cmCtMapList){
					cmCtMap.put(map.get("belong_cinema").toString(), map);
				}
				
				
//				sql = new StringBuffer();//各巡检服务商的巡检联系人（包含基本条件）
//				sql.append(" SELECT ");
//				sql.append(" 	dp.id AS dpId, ct.`name` AS ctName, ct.cellphone1 ");
//				sql.append(" FROM ");
//				sql.append(" 	t_s_base_user bu ");
//				sql.append(" LEFT JOIN zy_contacts ct ON ct.base_id = bu.ID ");
//				sql.append(" LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID ");
//				sql.append(" LEFT JOIN t_s_depart dp ON dp.ID = uo.org_id ");
//				sql.append(" LEFT JOIN t_s_role_user ru ON ru.userid = bu.ID ");
//				sql.append(" LEFT JOIN t_s_role ro ON ro.ID = ru.roleid ");
//				sql.append(" WHERE ");
//				sql.append(" 	ro.rolecode = '").append(StaticValue.RULE_CODE_THIRDPARTY_HEADER).append("' ");
//				sql.append(" AND ct.position = '").append(StaticValue.DIC_CONTACT_POSITION_TP_SERVER_BUSINESSR).append("' ");
//				sql.append(" AND ct.is_delete = '0' ");
//				sql.append(" AND ct.state = '").append(StaticValue.DIC_ENGINEER_STATE_ONJOB).append("' ");
//				sql.append(" AND dp.bus_scope LIKE '%").append(StaticValue.DIC_BUS_SCOPE_INSPECTION).append("%' ");
//				List<Map<String, Object>> ipCtsMapList = super.findForJdbc(sql.toString());
//				Map<String, Map<String, Object>> ipCtsMap = new HashMap<String, Map<String,Object>>();
//				for(Map<String, Object> map : ipCtsMapList){
//					ipCtsMap.put(map.get("dpId").toString(), map);
//				}
				
				sql = new StringBuffer();//查询所有第三方组织机构
				sql.append(" SELECT ");
				sql.append(" 	id, departname ");
				sql.append(" FROM ");
				sql.append(" 	t_s_depart dp");
				sql.append(" WHERE ");
				sql.append(" 	org_type = '").append(StaticValue.DIC_ORG_TYPE_THIRD_PARTY).append("' ");
				sql.append(" AND dp.logic_delete = 0 ");
				List<Map<String, Object>> tpMapList = super.findForJdbc(sql.toString());
				Map<String, Map<String, Object>> tpMap = new HashMap<String, Map<String, Object>>();
				for(Map<String, Object> map : tpMapList){
					tpMap.put(map.get("id").toString(), map);
				}
				
				
				//银幕类型
				List<TSType> tsTypes = super.findUniqueByProperty(TSTypegroup.class, "typegroupcode", StaticValue.DIC_SCREEN_TYPE).getTSTypes();
				Map<String, String> screenTypeMap = new HashMap<String, String>();
				for (TSType tsType : tsTypes) {
					screenTypeMap.put(tsType.getTypecode(), tsType.getTypename());
				}
				
				String fileName = "生成巡检计划.xlsx";
	            response.setCharacterEncoding("text/html;charset=utf-8");
	            response.setContentType("application/x-msdownload;");
	            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
	            out = response.getOutputStream();
				InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/cbsEsPlanIpTemplate.xlsx");
				XSSFWorkbook xssf = new XSSFWorkbook(is);
				XSSFSheet sheet = xssf.getSheetAt(0);
				int rowNum = 1;
				for (Map<String, Object> map : cipEsMapList) {
					XSSFRow row = sheet.createRow(rowNum);
					if(StringUtil.isNotEmpty(map.get("type_desc"))){
						row.createCell(0).setCellValue(map.get("type_desc").toString());
					}
					if(StringUtil.isNotEmpty(map.get("sn"))){
						row.createCell(1).setCellValue(map.get("sn").toString());
					}
					if(StringUtil.isNotEmpty(map.get("bp_no"))){
						row.createCell(2).setCellValue(map.get("bp_no").toString());
					}
					if(StringUtil.isNotEmpty(map.get("province"))){
						row.createCell(3).setCellValue(map.get("province").toString());
					}
					if(StringUtil.isNotEmpty(map.get("city"))){
						row.createCell(4).setCellValue(map.get("city").toString());
					}
					if(StringUtil.isNotEmpty(map.get("cmName"))){
						row.createCell(5).setCellValue(map.get("cmName").toString());
					}
					if(StringUtil.isNotEmpty(map.get("cmAddress"))){
						row.createCell(6).setCellValue(map.get("cmAddress").toString());
					}
					String cmId = map.get("cmId").toString();
					//影院经理
					if(ctsMap.get(cmId) != null&&
							ctsMap.get(cmId).get("position").toString().equals(StaticValue.DIC_CONTACT_POSITION_C_MANAGER)){
						if(StringUtil.isNotEmpty(ctsMap.get(cmId).get("name"))){
							row.createCell(7).setCellValue(ctsMap.get(cmId).get("name").toString());
						}
						if(StringUtil.isNotEmpty(ctsMap.get(cmId).get("cellphone1"))){
							row.createCell(8).setCellValue(ctsMap.get(cmId).get("cellphone1").toString());
						}
						if(StringUtil.isNotEmpty(ctsMap.get(cmId).get("fax"))){
							row.createCell(9).setCellValue(ctsMap.get(cmId).get("fax").toString());
						}
					}
					//影院技术负责人
					if(ctsMap.get(cmId) != null &&
							ctsMap.get(cmId).get("position").toString().equals(StaticValue.DIC_CONTACT_POSITION_C_TECHNICAL_DIRECTOR)){
						if(StringUtil.isNotEmpty(ctsMap.get(cmId).get("name"))){
							row.createCell(10).setCellValue(ctsMap.get(cmId).get("name").toString());
						}
						if(StringUtil.isNotEmpty(ctsMap.get(map.get("cmId").toString()).get("cellphone1"))){
							row.createCell(11).setCellValue(ctsMap.get(cmId).get("cellphone1").toString());
						}
					}
					row.createCell(12).setCellValue(tpMap.get(map.get("ip_company_id").toString()).get("departname").toString());
					row.createCell(13).setCellValue(map.get("ip_month").toString());
					//影院巡检联系人
					if(cmCtMap.get(cmId) != null){
						if(cmCtMap.get(cmId).get("name") != null){
							row.createCell(14).setCellValue(cmCtMap.get(cmId).get("name").toString());
						}
						if(cmCtMap.get(cmId).get("cellphone1") != null){
							row.createCell(15).setCellValue(cmCtMap.get(cmId).get("cellphone1").toString());
						}
					}
					if(StringUtil.isNotEmpty(map.get("install_date"))){
						row.createCell(16).setCellValue(map.get("install_date").toString());
					}
					if(StringUtil.isNotEmpty(map.get("last_year_inspection_date"))){
						row.createCell(17).setCellValue(map.get("last_year_inspection_date").toString());
					}
					//这里有四个列（18-19-20-21）没有可以查询的值，不设置
					if(StringUtil.isNotEmpty(map.get("room_num"))){
						row.createCell(22).setCellValue(map.get("room_num").toString());
					}
					if(StringUtil.isNotEmpty(map.get("screen_type"))){
						row.createCell(23).setCellValue(screenTypeMap.get(map.get("screen_type").toString()));
					}
					if(StringUtil.isNotEmpty(map.get("screen_plus"))){
						row.createCell(24).setCellValue(map.get("screen_plus").toString());
					}
					if(StringUtil.isNotEmpty(map.get("screen_width"))){
						row.createCell(25).setCellValue(map.get("screen_width").toString());
					}
					if(StringUtil.isNotEmpty(map.get("screen_hight"))){
						row.createCell(26).setCellValue(map.get("screen_hight").toString());
					}
					if(StringUtil.isNotEmpty(map.get("curtain_width"))){
						row.createCell(27).setCellValue(map.get("curtain_width").toString());
					}
					if(StringUtil.isNotEmpty(map.get("curtain_hight"))){
						row.createCell(28).setCellValue(map.get("curtain_hight").toString());
					}
					if(StringUtil.isNotEmpty(map.get("show_km"))){
						row.createCell(29).setCellValue(map.get("show_km").toString());
					}
					
				    rowNum++;
				}
				xssf.write(out);
			}else{
				j.setSuccess(false);
				j.setMsg("导出失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
			j.setSuccess(false);
			j.setMsg("导出失败");
		} finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
	}
	
}